package com.teach.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Select;
import org.springframework.data.repository.query.Param;
import com.teach.dto.CertificateInfoSearch;
import com.teach.dto.CertificateListAllSearch;
import com.teach.entity.CertificateInfo;

public interface CertificateInfoMapper {
    int deleteByPrimaryKey(Long infoId);

    int insert(CertificateInfo record);

    int insertSelective(CertificateInfo record);

    CertificateInfo selectByPrimaryKey(Long infoId);

    int updateByPrimaryKeySelective(CertificateInfo record);

    int updateByPrimaryKey(CertificateInfo record);

	 //查询部分字段(证书编号、证书类型编号、姓名、身份证号)
    CertificateInfo selectByPrimaryKeyPart(Long typeId);

    
    @Select("select * from certificate_info")
    List<CertificateInfo> selectAll();
    
    @Select("select * from certificate_info where info_certificate_id=#{infoCertificateId} ")
    CertificateInfo select(Long certificateInfoId);
      
    @Select("select a.info_certificate_id,a.info_certificate_type_id,a.info_name,a.info_card,a.info_img,a.info_birth,a.info_grade,b.type_id as typeId ,b.type_name as typeName,b.type_examination,b.type_time \r\n" + 
    		"from certificate_info as a\r\n" + 
    		"INNER JOIN certificate_type as b\r\n" + 
    		"where a.info_certificate_type_id=b.type_id and a.info_certificate_id=#{"
    		+ "infoCertificateId};")
    CertificateListAllSearch selectType( Long infoCertificateId);
    
  //查询是否已认证
    @Select("select  A.info_name,A.info_sex,A.info_card,A.info_phone,A.info_address,A.info_grade,A.info_img,A.info_remark,A.info_status,A.info_exam_username,A.info_rzs_username,A.info_zfb_number,B.type_name,B.type_designation\r\n"+
    		"from certificate_info as A\r\n" + 
    		"INNER JOIN certificate_type as B\r\n" +
    		"where a.info_certificate_type_id=b.type_id and a.info_pass='已通过' and A.info_status=0;"
    		)
//    List<CertificateInfo> selectByinfoStatus(@Param("infoStatus")Integer infoStatus);
    List<CertificateInfo> selectByinfoStatus(String infoPass,Integer infoStatus);
    //查询考生是否通过
    //查询考生已通过为认证
//  @Select("select * from certificate_info where info_pass=#{infoPass}")
  @Select("select  A.info_name,A.info_sex,A.info_card,A.info_phone,A.info_address,A.info_grade,A.info_img,A.info_remark,A.info_status,A.info_exam_username,A.info_rzs_username,A.info_zfb_number,B.type_name,B.type_designation\r\n"+
  		"from certificate_info as A\r\n" + 
  		"INNER JOIN certificate_type as B\r\n" +
  		"where a.info_certificate_type_id=b.type_id and a.info_pass='已通过' and A.info_status=1;"
  		)
  List<CertificateInfo> selectByinfoPass(String infoPass,Integer infoStatus);
//  List<CertificateInfo> selectByinfoPass(@Param("infoPass")String infoPass);
  
  //查询未认证
  @Select("select  A.info_name,A.info_sex,A.info_card,A.info_phone,A.info_address,A.info_grade,A.info_img,A.info_remark,A.info_status,A.info_exam_username,A.info_rzs_username,A.info_zfb_number,B.type_name,B.type_designation\r\n"+
  		"from certificate_info as A\r\n" + 
  		"INNER JOIN certificate_type as B\r\n" +
  		"where a.info_certificate_type_id=b.type_id and A.info_status=1;"
  		)
  List<CertificateInfo> selectByinfoStatus2(Integer infoStatus);
  
  //查询已认证
  @Select("select  A.info_name,A.info_sex,A.info_card,A.info_phone,A.info_address,A.info_grade,A.info_img,A.info_remark,A.info_status,A.info_exam_username,A.info_rzs_username,A.info_zfb_number,B.type_name,B.type_designation\r\n"+
  		"from certificate_info as A\r\n" + 
  		"INNER JOIN certificate_type as B\r\n" +
  		"where a.info_certificate_type_id=b.type_id and A.info_status=0;"
  		)
  List<CertificateInfo> selectByinfoStatus3(Integer infoStatus);
  
    List<CertificateInfo> select(CertificateInfoSearch search);
    
    List<CertificateInfo> selectPage(CertificateInfoSearch search);
    
}